# -*- coding: UTF-8 -*-
"""
@auth:buxiangjie
@date:2020-10-11 09:26:00
@describe: 数据库连接配置
"""
import pymysql
import json
import traceback
import sys
import os
import allure

from typing import Optional
from log.ulog import Ulog
from common.common import Common
from common.common import Encoder
from common.excep import excep

log = Ulog().logger_()


class ConnMysql:
	"""
	连接mysql
	"""

	@staticmethod
	@excep
	def conn_database(environment: str, source: Optional[str] = "saas") -> object:
		"""
		数据库连接
		"""
		yaml_data = Common.get_yaml_data("config", "database.yaml")
		config = yaml_data[environment][source]
		config["cursorclass"] = pymysql.cursors.DictCursor
		if config.get("password", None) is None:
			config["password"] = None
		else:
			config["password"] = str(config["password"])
		connect = pymysql.connect(**config)
		return connect

	@staticmethod
	def exec_update(
			sql: str,
			environment: str,
			source: Optional[str] = "saas"
	):
		"""执行更新语句"""
		conn = None
		cur = None
		try:
			conn = ConnMysql.conn_database(environment, source)
			cur = conn.cursor()
			log.info(f"执行更新语句:{sql}")
			cur.execute(sql)
			conn.commit()
		except Exception:
			conn.rollback()
			log.error(traceback.format_exc())
		finally:
			cur.close()
			conn.close()

	@staticmethod
	def exec_insert(
			sql: str,
			environment: str,
			source: Optional[str] = "saas"
	):
		"""执行插入语句"""
		conn = None
		cur = None
		try:
			conn = ConnMysql.conn_database(environment, source)
			cur = conn.cursor()
			log.info(f"执行插入语句:{sql}")
			cur.execute(sql)
			conn.commit()
		except Exception:
			conn.rollback()
			log.error(traceback.format_exc())
		finally:
			cur.close()
			conn.close()

	@staticmethod
	def exec_delete(
			sql: str,
			environment: str,
			source: Optional[str] = "saas"
	):
		"""执行删除语句"""
		conn = None
		cur = None
		try:
			conn = ConnMysql.conn_database(environment, source)
			cur = conn.cursor()
			log.info(f"执行删除语句:{sql}")
			cur.execute(sql)
			conn.commit()
		except Exception:
			conn.rollback()
			log.error(traceback.format_exc())
		finally:
			cur.close()
			conn.close()

	@staticmethod
	@excep
	@allure.step("执行查询sql")
	def exec_select(
			sql: str,
			environment: str,
			source: Optional[str] = "saas"
	) -> list:
		"""执行查询语句"""
		conn = None
		cur = None
		try:
			conn = ConnMysql.conn_database(environment, source)
			cur = conn.cursor()
			log.info(f"""执行查询语句:{sql}""")
			cur.execute(sql)
			fetch = cur.fetchall()
			result = None
			if len(fetch) > 0:
				result = json.loads(json.dumps(fetch, cls=Encoder))
			log.info(f"查询结果为:{result}")
			Common.get_rep_text(result)
			return result
		except Exception:
			log.error(traceback.format_exc())
		finally:
			cur.close()
			conn.close()

	@staticmethod
	@excep
	def get_sub_table(environment: str, asset_id: str) -> str:
		"""分表计算"""
		table = None
		if environment == "test":
			table = str((int(asset_id) % 8 + 1))
		elif environment in ("qa", "prod"):
			table = str((int(asset_id) >> 22) % 8 + 1)
		return table

	@staticmethod
	@excep
	def get_column(environment: str, source: str, table_name: str, col: list):
		"""获取剔除后的字段"""
		if len(col) == 1:
			col_str = f"column_name <> '{col[0]}'"
		elif len(col) > 1:
			new_list = []
			for i in col:
				new_list.append(f"column_name <> '{i}' ")
			col_str = "and ".join(new_list)
		col_sql = f"""
					select GROUP_CONCAT(column_name , ',') as col from information_schema.`COLUMNS` 
					where {col_str}
					and table_name='{table_name}'
					group by table_name;
				"""
		res = str(ConnMysql.exec_select(environment, col_sql, source)[0]["col"]).replace(",,", ",")
		log.info(f"获取要查询的字段")
		return res
